﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class OtherProvide
    {
        Database db;
        public OtherProvide()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        public DataSet getJobRank()
        {
            return getJobRank("");
        }

        public int DeleteJobRank(string JobRankID)
        {
            string strSQL = " DELETE FROM JobRank where JobRankID = " + JobRankID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int AddJobRank(string JobRankName,string JobRankLevel,string Status)
        {
            string strSQL = " INSERT INTO JobRank(JobRankName,JobRankLevel,[Status])VALUES('" + JobRankName + "','" + JobRankLevel + "','" + Status + "')";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateJobRank(string JobRankID, string JobRankName, string JobRankLevel, string Status)
        {
            string strSQL = "UPDATE JobRank SET JobRankName ='" + JobRankName + "',JobRankLevel='" + JobRankLevel + "',[Status]='" + Status + "' WHERE JobRankID= " + JobRankID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getAllJobRank()
        {
            string strSQL = "SELECT * FROM JobRank WHERE 1=1 Order by JobRankLevel";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getJobRank(string RankLevel)
        {
            string strSQL = "SELECT * FROM JobRank WHERE 1=1 AND [Status]='A' ";
            if (RankLevel.Trim().Length > 0)
            {
                strSQL += "   AND JobRankLevel > '" + RankLevel + "' ";
            }

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getJobRankByID(string JobRankID)
        {
            string strSQL = "SELECT * FROM JobRank WHERE JobRankID = " + JobRankID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getJobRanksByLevel(string RankLeveL)
        {
            string strSQL = "SELECT * FROM JobRank WHERE 1=1 AND [status] ='A' ";
            if (RankLeveL.Trim().Length > 0)
            {
                strSQL += "   AND JobRankLevel = '" + RankLeveL + "' ";
            }
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getJobRankName(string JobRankID)
        {
            string strSQL = " select JobRankName from dbo.JobRank where JobRankID = " + JobRankID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public string getJobRankLevelByJobRanK(string jobRankID)
        {
            string strSQL = " select JobRankLevel from jobRank where JobRankID = "+jobRankID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public string getLastApplyDate(string IDCard)
        {
            string strSQL = " SELECT DataEnteredDate FROM applybase WHERE IDCard = '" + IDCard + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        #region <<  外语管理  >>
        public string getLanguageName(string Language)
        {
            string strSQL = " SELECT ForeignLanguageName FROM ForeignLanguage WHERE ForeignLanguageid = '" + Language + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }
        public DataSet getLanguageType()
        {
            string strSQL = "SELECT * FROM ForeignLanguage WHERE 1=1 AND Status = 'A' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getAllLanguage(string strID)
        {
            string strSQL = "SELECT * FROM ForeignLanguage WHERE 1=1 ";
            if (!string.IsNullOrEmpty(strID))
            {
                strSQL += " AND ForeginID ='" + strID + "' ";
            }

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int InsertLanguage(string LanguageCode, string LanguageName, string status)
        {
            string strSQL = "INSERT INTO ForeignLanguage(ForeignLanguageid,ForeignLanguageName,status)VALUES('" + LanguageCode + "','" + LanguageName + "','" + status + "')";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int DeleteLanguage(string LanguageID)
        {
            string strSQL = "DELETE FROM ForeignLanguage WHERE ForeginID = '" + LanguageID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateLanguage(string LanguageCode, string LanguageName, string status, string ID)
        {
            string strSQL = "UPDATE ForeignLanguage SET ForeignLanguageid='" + LanguageCode + "',ForeignLanguageName='" + LanguageName + "',status='" + status + "' WHERE ForeginID ='" + ID + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion

        #region <<  下拉列表  >>

        public DataSet dstGetDropValues(string dictName)
        {
            string strSQL = "SELECT dict_Display,dict_Value FROM sys_dict WHERE dict_Name ='" + dictName + "' AND dict_status ='A' AND dict_Type='2' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getDictDisplayByValue(string Value, string dictName)
        {
            string strSQL = "SELECT dict_display FROM sys_dict WHERE dict_Name = '" + dictName + "' AND dict_value  = '" + Value + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return (string)Utility.ExecuteScalar(db, dbcommand);
        }

        public DataSet getApplyType()
        {
            return dstGetDropValues("ApplyType");
        }

        public DataSet dstGetComputerResult()
        {
            return dstGetDropValues("ComputerResult");
        }

        public DataSet dstGeteducationlevel()
        {
            return dstGetDropValues("educationlevel");
        }

        public DataSet dstGetFixedJob()
        {
            return dstGetDropValues("FixedJob");
        }

        public DataSet dstGetSpecialityLevel()
        {
            return dstGetDropValues("SpecialityLevel");
        }

        public DataSet dstGetForeignLanguageResult()
        {
            return dstGetDropValues("ForeignLanguageResult");
        }

        public DataSet dstGetGender()
        {
            return dstGetDropValues("Gender");
        }

        public DataSet dstGetAwardsLevel()
        {
            return dstGetDropValues("AwardsLevel");
        }

        public DataSet dstGetAwardsRank()
        {
            return dstGetDropValues("AwardsRank");
        }

        public DataSet dstGetSpecialitPersonalRank()
        {
            return dstGetDropValues("SpecialitPersonalRank");
        }

        public DataSet dstGetApplyStyle()
        {
            return dstGetDropValues("ApplyStyle");
        }

        public string getApplyStyleDispley(string value)
        {
            return getDictDisplayByValue(value, "ApplyStyle");
        }

        public DataSet dstGetApplyEvaluation()
        {
            return dstGetDropValues("ApplyEvaluation");
        }
        #endregion 
    }
}
